﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using OpenComposite.Base.Collections;
using OpenComposite.HumanActivity.Contracts;
using OpenComposite.HumanActivity.Contracts.Participants;

namespace OpenComposite.HumanActivity.Data
{
	internal class DataAccess
		: OpenComposite.Base.DataAccessBase
	{
		#region Initialization
		public DataAccess()
			: base(Properties.Settings.Default.ConnectionString)
		{

		}
		#endregion

		#region Activity Handling

		public bool CreateActivity(Guid workflowInstanceId,
				string activityName, string processName, string subject,
				int humanActivityId, string replyUri, string messageId,
				DateTime dueDate, out int activityId)
		{
			bool ok = false;
			activityId = -1;

			execNonQueryProcedure("add_workflow_instance",
				createSqlParam("@workflow_instance_id", workflowInstanceId),
				createSqlParam("@process_nm", processName));

			SqlParameter paramActivityId = new SqlParameter();
			paramActivityId.Direction = ParameterDirection.Output;
			paramActivityId.ParameterName = "@activity_id";
			paramActivityId.SqlDbType = SqlDbType.Int;

			execNonQueryProcedure("create_activity",
				createSqlParam("@workflow_instance_id", workflowInstanceId),
				createSqlParam("@activity_nm", activityName),
				createSqlParam("@process_nm", processName),
				createSqlParam("@subject", subject),
				createSqlParam("@hact_id", humanActivityId),
				createSqlParam("@reply_uri", replyUri),
				createSqlParam("@message_id", messageId),
				createSqlParam("@due_dt", dueDate),
				paramActivityId);

			if ( paramActivityId.Value is int ) {
				activityId = (int)paramActivityId.Value;
				ok = true;
			}

			return ok;
		}
		public bool UpdateActivity(int activityId, int userId,
			string activityName, string processName, string subject,
			string replyUri, string messageId)
		{
			int result = execNonQueryProcedure("update_activity",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@activity_nm", activityName),
				createSqlParam("@process_nm", processName),
				createSqlParam("@subject", subject),
				createSqlParam("@reply_uri", replyUri),
				createSqlParam("@message_id", messageId));
			return true;
		}

		public bool StartActivity(int activityId, int userId)
		{
			SqlParameter paramOutNewStatus = createOutSqlParam("@new_status_id", SqlDbType.Int);
			execNonQueryProcedure("start_activity",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				paramOutNewStatus);

			if ( paramOutNewStatus != null && paramOutNewStatus.Value == DBNull.Value ) {
				// sp start_activity returns an error
				return false; // EXIT
			}

			return ( paramOutNewStatus != null && paramOutNewStatus.Value is int &&
					( (ActivityStatus)paramOutNewStatus.Value ) == ActivityStatus.InProgress );
		}
		public bool AbortActivity(int activityId, int userId, string message, string details)
		{
			SqlParameter paramOutNewStatus = createOutSqlParam("@new_status_id", SqlDbType.Int);
			execNonQueryProcedure("abort_activity",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@abort_message", message),
				createSqlParam("@abort_details", details),
				paramOutNewStatus);
			return ( paramOutNewStatus != null && paramOutNewStatus.Value is int &&
					( (ActivityStatus)paramOutNewStatus.Value ) == ActivityStatus.Aborted );
		}
		public bool CancelActivity(int activityId, int userId)
		{
			SqlParameter paramOutNewStatus = createOutSqlParam("@new_status_id", SqlDbType.Int);
			execNonQueryProcedure("cancel_activity",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				paramOutNewStatus);
			return ( paramOutNewStatus != null && paramOutNewStatus.Value is int &&
					( (ActivityStatus)paramOutNewStatus.Value ) == ActivityStatus.NotStarted );
		}
		public bool CompleteActivity(int activityId, int userId)
		{
			SqlParameter paramOutNewStatus = createOutSqlParam("@new_status_id", SqlDbType.Int);
			execNonQueryProcedure("complete_activity",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				paramOutNewStatus);
			return ( paramOutNewStatus != null && paramOutNewStatus.Value is int &&
					( (ActivityStatus)paramOutNewStatus.Value ) == ActivityStatus.Completed );
		}
		public bool FaultActivity(int activityId, int userId, string message, string details)
		{
			SqlParameter paramOutNewStatus = createOutSqlParam("@new_status_id", SqlDbType.Int);
			int result = execNonQueryProcedure("fault_activity",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@fault_message", message),
				createSqlParam("@fault_details", details),
				paramOutNewStatus);
			return ( paramOutNewStatus != null && paramOutNewStatus.Value is int &&
					( (ActivityStatus)paramOutNewStatus.Value ) == ActivityStatus.Faulted );
		}
		public bool StopActivity(int activityId, int userId, string message, string details)
		{
			SqlParameter paramOutNewStatus = createOutSqlParam("@new_status_id", SqlDbType.Int);
			int result = execNonQueryProcedure("stop_activity",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@stop_message", message),
				createSqlParam("@stop_details", details),
				paramOutNewStatus);
			return ( paramOutNewStatus != null && paramOutNewStatus.Value is int &&
					( (ActivityStatus)paramOutNewStatus.Value ) == ActivityStatus.Stopped );
		}
		public bool PostPoneActivity(int activityId, int userId, DateTime postponeDateTime)
		{
			SqlParameter paramOutNewStatus = createOutSqlParam("@new_status_id", SqlDbType.Int);
			execNonQueryProcedure("postpone_activity",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@postpone_dt", postponeDateTime),
				paramOutNewStatus);
			return ( paramOutNewStatus != null && paramOutNewStatus.Value is int &&
					( (ActivityStatus)paramOutNewStatus.Value ) != ActivityStatus.Completed );
		}
		public ActivityItem GetActivity(int activityId, int userId)
		{
			DataSet dsResult = execProcedure("get_activity_form",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId));
			if ( dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0 ) {
				return ActivityItemFactory.Create(dsResult.Tables[0].Rows[0]);
			} else {
				return null;
			}
		}
		public List<ActivityItem> GetActivities()
		{
			DataSet dsResult = execProcedure("get_all_humanactivities");
			if ( dsResult != null && dsResult.Tables.Count > 0 ) {
				return ActivityItemFactory.CreateList(dsResult.Tables[0]);
			} else {
				return new List<ActivityItem>();
			}
		}
		public string GetHumanActivityUrl(int activityId)
		{
			string query = "SELECT ha.hact_url FROM human_activities as ha INNER JOIN activity_inbox as ai ON ha.hact_id = ai.hact_id WHERE ai.activity_id = @actId";
			return execScalar(query, new SqlParameter("actId", activityId)) as string;
		}

		#endregion

		#region Activity Inbox

		public List<ActivityItem> GetActivityInbox(int userId)
		{
			DataSet dsResult = execProcedure("get_activity_inbox",
				createSqlParam("@user_id", userId));
			List<ActivityItem> inbox;
			if ( dsResult != null && dsResult.Tables.Count == 1 ) {
				inbox = ActivityItemFactory.CreateList(dsResult.Tables[0]);
			} else {
				inbox = new List<ActivityItem>();
			}
			return inbox;
		}
		public BindingListEx<ActivityItem> GetActivityInbox(string processName, string activityName,
			DateTime createDateFrom, DateTime createDateTo, ActivityStatus[] status)
		{
			string query = "SELECT * FROM activity_inbox";
			string condition = "";
			List<SqlParameter> parameter = new List<SqlParameter>();
			if ( !string.IsNullOrEmpty(processName) ) {
				condition += "process_nm = @processName";
				parameter.Add(new SqlParameter("processName", processName));
			}
			if ( !string.IsNullOrEmpty(activityName) ) {
				if ( !string.IsNullOrEmpty(condition) ) condition += " AND ";
				condition += "activity_nm = @activityName";
				parameter.Add(new SqlParameter("activityName", activityName));
			}
			if ( createDateFrom != DateTime.MinValue ) {
				if ( !string.IsNullOrEmpty(condition) ) condition += " AND ";
				condition += "created_dt >= @createDateFrom";
				parameter.Add(new SqlParameter("createDateFrom", createDateFrom));

			}
			if ( createDateTo != DateTime.MinValue ) {
				if ( !string.IsNullOrEmpty(condition) ) condition += " AND ";
				condition += "created_dt >= @createDateTo";
				parameter.Add(new SqlParameter("createDateTo", createDateTo));

			}
			if ( status != null && status.Length > 0 ) {
				if ( !string.IsNullOrEmpty(condition) ) condition += " AND ";
				condition += "(";
				for ( int i = 0; i < status.Length; i++ ) {
					condition += string.Format("status_id = @status{0}", i);
					if ( i != status.Length - 1 ) condition += " OR ";
					parameter.Add(new SqlParameter(string.Format("status{0}", i), status[i]));
				}
				condition += ")";
			}
			if ( !string.IsNullOrEmpty(condition) ) query += string.Format(" WHERE {0}", condition);
			DataTable dt = fillDataTable(query, parameter.ToArray());

			BindingListEx<ActivityItem> inbox = new BindingListEx<ActivityItem>();
			if ( dt != null ) {
				inbox.AddRange(ActivityItemFactory.CreateList(dt));
			}
			return inbox;
		}
		public List<string> GetAdminProcessNames()
		{
			string query = "SELECT DISTINCT process_nm FROM activity_inbox";
			DataTable dt = fillDataTable(query);
			List<string> processes = new List<string>();
			foreach ( DataRow row in dt.Rows ) {
				processes.Add(row["process_nm"] as string);
			}
			if ( !processes.Contains("") ) processes.Insert(0, "");
			return processes;
		}
		public List<string> GetAdminActivityNames(string process)
		{
			string query = "SELECT DISTINCT activity_nm FROM activity_inbox";
			DataTable dt = null;
			if ( !string.IsNullOrEmpty(process) ) {
				query += " WHERE process_nm = @process";
				dt = fillDataTable(query, new SqlParameter("process", process));
			} else {
				dt = fillDataTable(query);
			}
			List<string> activities = new List<string>();
			foreach ( DataRow row in dt.Rows ) {
				activities.Add(row["activity_nm"] as string);
			}
			if ( !activities.Contains("") ) activities.Insert(0, "");
			return activities;
		}
		public void SetActivityStatus(string action, int activityId, int userId)
		{
			string query = "UPDATE activity_inbox SET status_id = @status WHERE activity_id = @id";
			int statusId = 0;
			string message;

			//		NotStarted = 0,
			//		InProgress = 1,
			//		Completed = 2,
			//		Aborted = 3,
			//		Faulted = 4,

			switch ( action ) {
				case "reset":
					statusId = 0;
					break;
				case "start":
					StartActivity(activityId, userId);
					return;
				case "complete":
					CompleteActivity(activityId, userId);
					return;
				case "stop":
					AbortActivity(activityId, userId, "Stopped by process administration", null);
					return;
				case "fault":
					FaultActivity(activityId, userId, "Manually faulted", null);
					return;
				default:
					return;
			}
			execNonQuery(query,
				new SqlParameter("status", statusId),
				new SqlParameter("id", activityId));
		}

		#endregion

		#region Activity Data

		public bool SetActivityInputData(int activityId, int userId, XmlNode xnData)
		{
			int result = execNonQueryProcedure("set_activity_inputdata",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@data", xnData != null ? xnData.OuterXml : null, null),
				createSqlParam("@soap_envelope", DBNull.Value));
			return true;
		}
		public bool SetActivityFormData(int activityId, int userId, XmlNode xnData)
		{
			int result = execNonQueryProcedure("set_activity_formdata",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@data", xnData != null ? xnData.OuterXml : null, null),
				createSqlParam("@soap_envelope", DBNull.Value));
			return true;
		}
		public bool SetActivityOutputData(int activityId, int userId, XmlNode xnData)
		{
			int result = execNonQueryProcedure("set_activity_outputdata",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@data", xnData != null ? xnData.OuterXml : null, null),
				createSqlParam("@soap_envelope", DBNull.Value));
			return true;
		}

		public XmlNode GetActivityInputData(int activityId)
		{
			XmlNode xnResult = null;
			DataSet dsResult = execProcedure("get_activity_inputdata",
				createSqlParam("@activity_id", activityId));

			if ( dsResult != null && dsResult.Tables.Count == 1 && dsResult.Tables[0].Rows.Count == 1 &&
				 !dsResult.Tables[0].Rows[0].IsNull("data") && dsResult.Tables[0].Rows[0]["data"] is string ) {
				XmlDocument xd = new XmlDocument();
				try {
					xd.LoadXml((string)dsResult.Tables[0].Rows[0]["data"]);
					xnResult = xd.DocumentElement;
				} catch {
					xnResult = null;
				}
			}
			return xnResult;
		}
		public XmlNode GetActivityFormData(int activityId)
		{
			XmlNode xnResult = null;
			DataSet dsResult = execProcedure("get_activity_formdata",
				createSqlParam("@activity_id", activityId));

			if ( dsResult != null && dsResult.Tables.Count == 1 && dsResult.Tables[0].Rows.Count == 1 &&
				 !dsResult.Tables[0].Rows[0].IsNull("data") && dsResult.Tables[0].Rows[0]["data"] is string ) {
				XmlDocument xd = new XmlDocument();
				try {
					xd.LoadXml((string)dsResult.Tables[0].Rows[0]["data"]);
					xnResult = xd.DocumentElement;
				} catch {
					xnResult = null;
				}
			}
			return xnResult;
		}
		public XmlNode GetActivityOutputData(int activityId)
		{
			XmlNode xnResult = null;
			DataSet dsResult = execProcedure("get_activity_outputdata",
				createSqlParam("@activity_id", activityId));

			if ( dsResult != null && dsResult.Tables.Count == 1 && dsResult.Tables[0].Rows.Count == 1 &&
				 !dsResult.Tables[0].Rows[0].IsNull("data") && dsResult.Tables[0].Rows[0]["data"] is string ) {
				XmlDocument xd = new XmlDocument();
				try {
					xd.LoadXml((string)dsResult.Tables[0].Rows[0]["data"]);
					xnResult = xd.DocumentElement;
				} catch {
					xnResult = null;
				}
			}
			return xnResult;
		}

		public XmlNode GetCurrentActivityData(int activityId, int userId)
		{
			XmlNode xnResult = null;
			DataSet dsResult = execProcedure("get_activity_data",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId));

			if ( dsResult != null && dsResult.Tables.Count == 1 && dsResult.Tables[0].Rows.Count == 1 &&
				 !dsResult.Tables[0].Rows[0].IsNull("data") && dsResult.Tables[0].Rows[0]["data"] is string ) {
				XmlDocument xd = new XmlDocument();
				try {
					xd.LoadXml((string)dsResult.Tables[0].Rows[0]["data"]);
					xnResult = xd.DocumentElement;
				} catch {
					xnResult = null;
				}
			}
			return xnResult;
		}

		#endregion

		#region Activity Log
		public bool AddActivityLog(int activityId, int userId, ActivityLogType logType,
								   string message, string details)
		{
			int result = execNonQueryProcedure("add_activity_log",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@activity_log_type_id", (int)logType),
				createSqlParam("@activity_log_text", message),
				createSqlParam("@activity_log_details", details),
				createSqlParam("@user_id", userId));

			return result > 0;
		}
		public DataTable GetActivityLog(int activityId, int userId)
		{
			DataSet ds = execProcedure("get_activity_log",
				createSqlParam("@activity_id", activityId, -1),
				createSqlParam("@user_id", userId, -1));

			return ( ds.Tables.Count > 0 ? ds.Tables[0] : null );
		}
		#endregion

		#region Human Activity
		public DataTable GetHumanActivities()
		{
			return fillDataTable("SELECT * FROM human_activities WHERE is_deleted = 0");
		}
		public void SetHumanActivityURL(int hactID, string url)
		{
			execNonQuery(
				//"DECLARE @oldurl nvarchar(2000); " +
				//"SELECT @oldurl = hact_url FROM human_activities WHERE hact_id = @hact_id; " +
				//"UPDATE process_activity SET activityservice_url = @hact_url " +
				//    "WHERE activityservice_url = @oldurl; " +
				"UPDATE human_activities SET hact_url = @hact_url WHERE hact_id = @hact_id;",
				new SqlParameter("@hact_url", url),
				new SqlParameter("@hact_id", (object)hactID));
		}
		public void SetHumanActivityUpdateUrl(Guid identifier, string url)
		{
			string configKey = "HumanActivityUpdate";
			execNonQuery("DELETE FROM configurations WHERE config_key = @key",
				new SqlParameter("key", configKey));

			execNonQuery("INSERT INTO configurations(config_guid, config_key, config_value) VALUES(@guid, @key, @value)",
				new SqlParameter("guid", identifier),
				new SqlParameter("key", configKey),
				new SqlParameter("value", url));
		}
		public void SetHumanActivityPort(int port)
		{
			string configKey = "HumanActivityPort";
			execNonQuery("DELETE FROM configurations WHERE config_key = @key",
				new SqlParameter("key", configKey));

			execNonQuery("INSERT INTO configurations(config_key, config_value) VALUES(@key, @value)",
				new SqlParameter("key", configKey),
				new SqlParameter("value", port.ToString()));
		}
		#endregion

		#region Attachments
		public bool AddAttachment(Guid workflowInstanceId, int? activityId,
								  string attachmentName, string attachmentDescription,
								  string attachmentFileName, byte[] attachmentData,
								  int createdByUserId, out int attachmentId)
		{
			attachmentId = -1;

			SqlParameter poutAttachmentId = new SqlParameter();
			poutAttachmentId.ParameterName = "@attachment_id";
			poutAttachmentId.Direction = ParameterDirection.InputOutput;
			poutAttachmentId.SqlDbType = SqlDbType.Int;
			poutAttachmentId.Value = DBNull.Value;

			int result = execNonQueryProcedure("add_attachment",
				createSqlParam("@workflow_instance_id", workflowInstanceId, Guid.Empty),
				createSqlParam("@activity_id", activityId),
				createSqlParam("@attachment_nm", attachmentName),
				createSqlParam("@attachment_desc", attachmentDescription),
				createSqlParam("@attachment_filenm", attachmentFileName),
				createSqlParam("@attachment_data", attachmentData),
				createSqlParam("@created_by_user_id", createdByUserId),
				poutAttachmentId);

			if ( result > 0 && poutAttachmentId.Value is int ) {
				attachmentId = (int)poutAttachmentId.Value;
			}
			return ( attachmentId > 0 );
		}
		public bool UpdateAttachment(int attachmentId, string attachmentName,
			string attachmentDescription, string attachmentFileName, byte[] attachmentData,
			int? lastModifiedByUserId)
		{
			int result = execNonQueryProcedure("update_attachment",
				createSqlParam("@attachment_id", attachmentId),
				createSqlParam("@attachment_nm", attachmentName),
				createSqlParam("@attachment_desc", attachmentDescription),
				createSqlParam("@attachment_filenm", attachmentFileName),
				createSqlParam("@attachment_data", attachmentData),
				createSqlParam("@user_id", lastModifiedByUserId));
			return ( result > 0 );
		}
		public bool DeleteAttachment(int attachmentId, int userId)
		{
			int result = execNonQueryProcedure("delete_attachment",
				createSqlParam("@attachment_id", attachmentId),
				createSqlParam("@user_id", userId));

			return ( result > 0 );
		}
		public byte[] GetAttachmentData(int attachmentId)
		{
			object objResult = execScalarProcedure("get_attachment_data",
				createSqlParam("@attachment_id", attachmentId));

			return objResult as byte[];
		}
		public List<AttachmentItem> GetAttachmentsList(Guid workflowInstanceId)
		{
			DataSet dsResult = execProcedure("get_attachments_list",
				createSqlParam("@workflow_instance_id", workflowInstanceId));

			List<AttachmentItem> attachments = new List<AttachmentItem>();
			if ( dsResult != null && dsResult.Tables.Count == 1 ) {
				foreach ( DataRow row in dsResult.Tables[0].Rows ) {
					attachments.Add(new AttachmentItem(row));
				}
			}
			return attachments;
		}
		#endregion

		#region Notes
		public bool AddNote(Guid workflowInstanceId, int? activityId,
							string noteSubject, string noteText,
							int createdByUserId, out int noteId)
		{
			noteId = -1;

			SqlParameter poutNoteId = new SqlParameter();
			poutNoteId.ParameterName = "@note_id";
			poutNoteId.Direction = ParameterDirection.InputOutput;
			poutNoteId.SqlDbType = SqlDbType.Int;
			poutNoteId.Value = DBNull.Value;

			int result = execNonQueryProcedure("add_note",
				createSqlParam("@workflow_instance_id", workflowInstanceId, Guid.Empty),
				createSqlParam("@activity_id", activityId, -1),
				createSqlParam("@note_subject", noteSubject),
				createSqlParam("@note_txt", noteText),
				createSqlParam("@created_by_user_id", createdByUserId),
				poutNoteId);

			if ( result > 0 && poutNoteId.Value is int ) {
				noteId = (int)poutNoteId.Value;
			}
			return ( noteId > 0 );
		}
		public bool UpdateNote(int noteId, string noteSubject, string noteText, int userId)
		{
			int result = execNonQueryProcedure("update_note",
							createSqlParam("@note_id", noteId),
							createSqlParam("@note_subject", noteSubject),
							createSqlParam("@note_txt", noteText),
							createSqlParam("@user_id", userId));
			return ( result > 0 );
		}
		public bool DeleteNote(int noteId)
		{
			int result = execNonQueryProcedure("delete_note",
				createSqlParam("@note_id", noteId));

			return ( result > 0 );
		}
		public List<NoteItem> GetNotesByWorkflowInstance(Guid workflowInstanceId)
		{
			DataSet dsResult = execProcedure("get_notes",
				createSqlParam("@workflow_instance_id", workflowInstanceId, Guid.Empty));

			return fillNotesList(dsResult);
		}
		public List<NoteItem> GetNotesByActivity(int activityId)
		{
			DataSet dsResult = execProcedure("get_notes",
				createSqlParam("@activity_id", activityId, -1));

			return fillNotesList(dsResult);
		}

		private List<NoteItem> fillNotesList(DataSet dsNotes)
		{
			List<NoteItem> notes = new List<NoteItem>();
			if ( dsNotes != null && dsNotes.Tables.Count == 1 ) {
				foreach ( DataRow row in dsNotes.Tables[0].Rows ) {
					notes.Add(new NoteItem(row));
				}
			}
			return notes;
		}
		#endregion

		#region Participants

		public bool AddParticipants(int activityId, IEnumerable<ParticipantItem> participants)
		{
			bool ok = true;
			SqlTransaction trans = beginTransaction("AddParticipants");
			try {
				foreach ( var part in participants ) {
					switch ( part.ParticipantType ) {
						case ParticipantType.Organization:
							addOrgParticipant(trans, activityId, part.ID, part.ParticipationType);
							break;
						case ParticipantType.OrganizationUnit:
							addOrgUnitParticipant(trans, activityId, part.ID, part.ParticipationType);
							break;
						case ParticipantType.Role:
							addRoleParticipant(trans, activityId, part.ID, part.ParticipationType);
							break;
						case ParticipantType.User:
							addUserParticipant(trans, activityId, part.ID, part.ParticipationType);
							break;
						case ParticipantType.ActivityUser:
							addActivityUserParticipant(trans, activityId, part.ID, part.ParticipationType);
							break;
					}
					if ( !ok ) {
						throw new ParticipantException(part);
					}
				}
			} catch {
				ok = false;
				rollbackTransaction(trans);
			} finally {
				if ( ok ) commitTransaction(trans);
			}
			return ok;
		}
		#region Private Add Participants Methods
		private bool addOrgParticipant(SqlTransaction trans, int activityId, int orgId, ParticipationType participationType)
		{
			int result = execNonQueryProcedure(trans, "add_org_participant",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@org_id", orgId),
				createSqlParam("@participation_type_id", (int)participationType));
			return ( result > 0 );
		}
		private bool addOrgUnitParticipant(SqlTransaction trans, int activityId, int orgUnitId, ParticipationType participationType)
		{
			int result = execNonQueryProcedure(trans, "add_orgunit_participant",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@org_unit_id", orgUnitId),
				createSqlParam("@participation_type_id", (int)participationType));
			return ( result > 0 );
		}
		private bool addRoleParticipant(SqlTransaction trans, int activityId, int roleId, ParticipationType participationType)
		{
			int result = execNonQueryProcedure(trans, "add_role_participant",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@role_id", roleId),
				createSqlParam("@participation_type_id", (int)participationType));
			return ( result > 0 );
		}
		private bool addUserParticipant(SqlTransaction trans, int activityId, int userId, ParticipationType participationType)
		{
			int result = execNonQueryProcedure(trans, "add_user_participant",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_id", userId),
				createSqlParam("@participation_type_id", (int)participationType));
			return ( result > 0 );
		}
		private bool addActivityUserParticipant(SqlTransaction trans, int activityId, int userOfActivityId, ParticipationType participationType)
		{
			int result = execNonQueryProcedure(trans, "add_activityuser_participant",
				createSqlParam("@activity_id", activityId),
				createSqlParam("@user_of_activity_id", userOfActivityId),
				createSqlParam("@participation_type_id", (int)participationType));
			return ( result > 0 );
		}
		#endregion
		public bool RemoveParticipants(int activityId, IEnumerable<ParticipantItem> participantsToRemove)
		{
			bool ok = true;
			int result = -1;

			SqlTransaction trans = beginTransaction("RemoveParts");
			try {
				foreach ( var participant in participantsToRemove ) {
					switch ( participant.ParticipantType ) {
						case ParticipantType.Organization:
							result = execNonQueryProcedure(trans, "remove_org_participant",
								createSqlParam("@activity_id", activityId),
								createSqlParam("@org_id", participant.ID),
								createSqlParam("@participation_type_id", (int)participant.ParticipationType));
							break;
						case ParticipantType.OrganizationUnit:
							result = execNonQueryProcedure(trans, "remove_orgunit_participant",
								createSqlParam("@activity_id", activityId),
								createSqlParam("@org_unit_id", participant.ID),
								createSqlParam("@participation_type_id", (int)participant.ParticipationType));
							break;
						case ParticipantType.Role:
							result = execNonQueryProcedure(trans, "remove_role_participant",
								createSqlParam("@activity_id", activityId),
								createSqlParam("@role_id", participant.ID),
								createSqlParam("@participation_type_id", (int)participant.ParticipationType));
							break;
						case ParticipantType.User:
							result = execNonQueryProcedure(trans, "remove_user_participant",
								createSqlParam("@activity_id", activityId),
								createSqlParam("@user_id", participant.ID),
								createSqlParam("@participation_type_id", (int)participant.ParticipationType));
							break;
						case ParticipantType.ActivityUser:
							result = execNonQueryProcedure(trans, "remove_activityuser_participant",
								createSqlParam("@activity_id", activityId),
								createSqlParam("@user_of_activity_id", participant.ID),
								createSqlParam("@participation_type_id", (int)participant.ParticipationType));
							break;
					}
				}
			} catch {
				ok = false;
				rollbackTransaction(trans);
			} finally {
				if ( ok ) commitTransaction(trans);
			}
			return ok;
		}
		public BindingListEx<ParticipantItem> GetParticipants(int activityId)
		{
			BindingListEx<ParticipantItem> participants = new BindingListEx<ParticipantItem>();

			DataSet dsOrgs = execProcedure("get_org_participants", createSqlParam("@activity_id", activityId));
			DataSet dsOrgUnits = execProcedure("get_orgunit_participants", createSqlParam("@activity_id", activityId));
			DataSet dsRoles = execProcedure("get_role_participants", createSqlParam("@activity_id", activityId));
			DataSet dsUsers = execProcedure("get_user_participants", createSqlParam("@activity_id", activityId));
			DataSet dsActUsers = execProcedure("get_activityuser_participants", createSqlParam("@activity_id", activityId));

			if ( dsOrgs != null && dsOrgs.Tables.Count == 1 ) {
				foreach ( DataRow row in dsOrgs.Tables[0].Rows ) {
					participants.Add(new ParticipantItem(
						ParticipantType.Organization,
						(int)row["org_id"],
						(ParticipationType)row["participation_type_id"]));
				}
			}
			if ( dsOrgUnits != null && dsOrgUnits.Tables.Count == 1 ) {
				foreach ( DataRow row in dsOrgUnits.Tables[0].Rows ) {
					participants.Add(new ParticipantItem(
						ParticipantType.OrganizationUnit,
						(int)row["org_unit_id"],
						(ParticipationType)row["participation_type_id"]));
				}
			}
			if ( dsRoles != null && dsRoles.Tables.Count == 1 ) {
				foreach ( DataRow row in dsRoles.Tables[0].Rows ) {
					participants.Add(new ParticipantItem(
						ParticipantType.Role,
						(int)row["role_id"],
						(ParticipationType)row["participation_type_id"]));
				}
			}
			if ( dsUsers != null && dsUsers.Tables.Count == 1 ) {
				foreach ( DataRow row in dsUsers.Tables[0].Rows ) {
					participants.Add(new ParticipantItem(
						ParticipantType.User,
						(int)row["user_id"],
						(ParticipationType)row["participation_type_id"]));
				}
			}
			if ( dsActUsers != null && dsActUsers.Tables.Count == 1 ) {
				foreach ( DataRow row in dsActUsers.Tables[0].Rows ) {
					participants.Add(new ParticipantItem(
						ParticipantType.ActivityUser,
						(int)row["user_of_activity_id"],
						(ParticipationType)row["participation_type_id"]));
				}
			}

			return participants;
		}

		public bool SetParticipants(int activityId, IEnumerable<ParticipantItem> participants)
		{
			bool ok = true;

			BindingListEx<ParticipantItem> oldParticipants = GetParticipants(activityId);
			BindingListEx<ParticipantItem> newParticipants = new BindingListEx<ParticipantItem>();

			foreach ( var part in participants ) {
				if ( oldParticipants.Contains(part) ) {
					oldParticipants.Remove(part);
				} else {
					newParticipants.Add(part);
				}
			}

			AddParticipants(activityId, newParticipants);
			RemoveParticipants(activityId, oldParticipants);

			return ok;
		}

		#endregion

		#region Log In / Out
		public LogInData LogIn(
			string sLogInName, string sPassWord, int iOrgId, int iLanguageId, string sOrgName, Guid applicationId)
		{
			DataSet ds = execProcedure("login_user",
				createSqlParam("@user_nm", sLogInName),
				createSqlParam("@password", sPassWord),
				createSqlParam("@org_id", iOrgId),
				createSqlParam("@lang_id", iLanguageId),
				createSqlParam("@org_nm", sOrgName),
				createSqlParam("@application_id", applicationId));

			LogInData logInUser = null;
			if ( ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0 ) {
				DataRow row = ds.Tables[0].Rows[0];

				logInUser = new LogInData();

				logInUser.ReturnCode = row["return_code"].ToString();
				logInUser.ReturnMessage = row["return_message"].ToString();

				if ( logInUser.ReturnCode.Equals("OK") ) {
					logInUser.UserID = Convert.ToInt32(row["user_id"]);
					logInUser.LanguageID = (int)row["lang_id"];
					logInUser.OrganizationID = (int)row["org_id"];
					logInUser.UserLCID = row.IsNull("user_lcid") ? 0 : (int)row["user_lcid"];
					logInUser.IsSystemAdministrator = (bool)row["sa_ind"];
					logInUser.CanUpdate = row.IsNull("can_update_ind") ? true : (bool)row["can_update_ind"];
					logInUser.UserTimeZoneOffSet = row.IsNull("user_tz_offset") ? 0 : Convert.ToDecimal(row["user_tz_offset"]);
					logInUser.RealUserTimeZoneOffSet = row.IsNull("real_user_tz_offset") ? 0 : Convert.ToDecimal(row["real_user_tz_offset"]);
					logInUser.LoginId = (Guid)row["login_id"];
					logInUser.FullName = string.Format("{0} {2}{1}", row["first_nm"], row["last_nm"], row["middle_nm"] == DBNull.Value ? "" : string.Format("{0}", row["middle_nm"]));
				} else if ( logInUser.ReturnCode == "USER_IS_LOGGED_IN" ) {
					logInUser.UserID = Convert.ToInt32(row["user_id"]);
					logInUser.LoginId = (Guid)row["login_id"];
				}
			}
			return logInUser;
		}
		public bool LogOut(LogInData userdata, Guid applicationId)
		{
			DataSet ds = execProcedure("logout_user",
				createSqlParam("@login_id", userdata.LoginId),
				createSqlParam("@application_id", applicationId));
			return true;
		}
		#endregion

		#region User Data

		public List<Language> GetLanguages()
		{
			List<Language> langs = new List<Language>();
			DataTable dt = fillDataTable("SELECT * FROM language");
			foreach ( DataRow row in dt.Rows ) {
				Language l = new Language()
				{
					LanguageID = (int)row["lang_id"],
					LanguageName = (string)row["lang_nm"]
				};
				langs.Add(l);
			}
			return langs;
		}

		public UserPreferences GetUserPreferences(LogInData userdata)
		{
			if ( userdata == null || !userdata.UserID.HasValue || userdata.UserID <= 0 ) {
				throw new Exception("Invalid login informations. Please logout and try again.");
			}
			Hashtable htRow = executeRow(
				"SELECT lcid, lang_id, email_activity_ind, email_addr, timezone_id " +
				"FROM users " +
				"WHERE user_id = @user_id",
				createSqlParam("@user_id", userdata.UserID));
			return new UserPreferences()
			{
				LCID = ( htRow["lcid"] == null ? 0 : (int)htRow["lcid"] ),
				LanguageID = ( htRow["lang_id"] == null ? 0 : (int)htRow["lang_id"] ),
				EnableEMailActivity = ( htRow["email_activity_ind"] == null ? false : (bool)htRow["email_activity_ind"] ),
				EMailAddress = htRow["email_addr"] as string,
				TimezoneId = htRow["timezone_id"] as string
			};
		}
		public bool SetUserPreferences(LogInData userdata, UserPreferences userpreferences)
		{
			if ( userdata == null || !userdata.UserID.HasValue || userdata.UserID <= 0 ) {
				throw new Exception("Invalid login informations. Please logout and try again.");
			}
			if ( userpreferences == null ) {
				throw new Exception("Invalid user preferences.");
			}
			int result = execNonQuery(
				"UPDATE users " +
				"SET lcid = @lcid, lang_id = @lang_id, email_activity_ind = @email_activity_ind, " +
					"email_addr = @email_addr, timezone_id = @timezone_id " +
				"WHERE user_id = @user_id",
				createSqlParam("@lcid", userpreferences.LCID),
				createSqlParam("@lang_id", userpreferences.LanguageID),
				createSqlParam("@email_activity_ind", userpreferences.EnableEMailActivity),
				createSqlParam("@email_addr", userpreferences.EMailAddress),
				createSqlParam("@timezone_id", userpreferences.TimezoneId),
				createSqlParam("@user_id", userdata.UserID));

			return ( result == 1 );
		}

		public PersonalUserData GetPersonalUserData(LogInData userdata)
		{
			if ( userdata == null || !userdata.UserID.HasValue || userdata.UserID <= 0 ) {
				throw new Exception("Invalid login informations. Please logout and try again.");
			}

			// read personal user data
			Hashtable ht = executeRow(
				"SELECT * FROM users WHERE user_id = @user_id",
				createSqlParam("@user_id", userdata.UserID.Value));

			if ( ht == null || ht.Count == 0 ) {
				throw new Exception("Unknown user.");
			}

			// set PersonalUserData object
			PersonalUserData data = new PersonalUserData()
			{
				Address = ht["addr"] as string,
				AssistantPhone = ht["assistant_phone"] as string,
				BusinessPhone = ht["work_phone"] as string,
				BusinessPhone2 = ht["business_phone_2"] as string,
				City = ht["city"] as string,
				Company = ht["company"] as string,
				Country = ht["country"] as string,
				Department = ht["department"] as string,
				EMailAddress = ht["email_addr"] as string,
				Fax = ht["fax_no"] as string,
				FirstName = ht["first_nm"] as string,
				HomePhone = ht["home_phone"] as string,
				HomePhone2 = ht["home_phone_2"] as string,
				LastName = ht["last_nm"] as string,
				MiddleName = ht["middle_nm"] as string,
				MobilePhone = ht["mobile_phone"] as string,
				Office = ht["office"] as string,
				Pager = ht["pager"] as string,
				State = ht["state"] as string,
				Title = ht["title"] as string,
				Zip = ht["zip"] as string
			};

			return data;
		}
		public bool SetPersonalUserData(LogInData userdata, PersonalUserData personaldata)
		{
			if ( userdata == null || !userdata.UserID.HasValue || userdata.UserID <= 0 ) {
				throw new Exception("Invalid login informations. Please logout and try again.");
			}
			int result = execNonQuery(
				"UPDATE users " +
				"SET " +
					"addr = @addr, assistant_phone = @assistant_phone, " +
					"work_phone = @work_phone, business_phone_2 = @business_phone_2, " +
					"city = @city, company = @company, " +
					"country = @country, department = @department, " +
					"email_addr = @email_addr, fax_no = @fax_no, " +
					"first_nm = @first_nm, home_phone = @home_phone, " +
					"home_phone_2 = @home_phone_2, last_nm = @last_nm, " +
					"middle_nm = @middle_nm, mobile_phone = @mobile_phone, " +
					"office = @office, pager = @pager, " +
					"state = @state, title = @title, zip = @zip " +
				"WHERE user_id = @user_id",
				createSqlParam("@addr", personaldata.Address),
				createSqlParam("@assistant_phone", personaldata.AssistantPhone),
				createSqlParam("@work_phone", personaldata.BusinessPhone),
				createSqlParam("@business_phone_2", personaldata.BusinessPhone2),
				createSqlParam("@city", personaldata.City),
				createSqlParam("@company", personaldata.Company),
				createSqlParam("@country", personaldata.Country),
				createSqlParam("@department", personaldata.Department),
				createSqlParam("@email_addr", personaldata.EMailAddress),
				createSqlParam("@fax_no", personaldata.Fax),
				createSqlParam("@first_nm", personaldata.FirstName),
				createSqlParam("@home_phone", personaldata.HomePhone),
				createSqlParam("@home_phone_2", personaldata.HomePhone2),
				createSqlParam("@last_nm", personaldata.LastName),
				createSqlParam("@middle_nm", personaldata.MiddleName),
				createSqlParam("@mobile_phone", personaldata.MobilePhone),
				createSqlParam("@office", personaldata.Office),
				createSqlParam("@pager", personaldata.Pager),
				createSqlParam("@state", personaldata.State),
				createSqlParam("@title", personaldata.Title),
				createSqlParam("@zip", personaldata.Zip),
				createSqlParam("@user_id", userdata.UserID));

			return ( result == 1 );
		}

		public bool UpdateUserPassword(LogInData userdata, string passwordOld, string passwordNew)
		{
			if ( userdata == null || !userdata.UserID.HasValue || userdata.UserID <= 0 ) {
				throw new Exception("Invalid login informations. Please logout and try again.");
			}
			// validate old password
			string passwordFromDB = execScalar(
				"SELECT password FROM users WHERE user_id = @user_id",
				createSqlParam("@user_id", userdata.UserID.Value)) as string;

			if ( passwordFromDB != passwordOld ) {
				throw new Exception("Invalid old password.");
			}

			// update password
			int result = execNonQuery(
				"UPDATE users SET password = @passwordNew WHERE user_id = @user_id",
				createSqlParam("@passwordNew", passwordNew),
				createSqlParam("@user_id", userdata.UserID.Value));

			return ( result == 1 );
		}
		#endregion
	}
}
